package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import com.model.Class;
import com.model.PublishExamPlan;
import com.model.QuestionInfo;
import com.util.DBUtil;
import com.util.Mode;
import com.util.ModelPublishExamPlan;

public class PublishExamPlanDao {
	Connection conn;
	PreparedStatement ps;
	ResultSet rs;
	
	public int insert(PublishExamPlan pe) {
		int row=-1;
		conn=DBUtil.getConn();
		try {
			ps=conn.prepareStatement("insert into publishExamPlan values(null,?,?,?,?,?,1)");
			ps.setInt(1, pe.getPaperId());
			ps.setInt(2, pe.getClassId());
			ps.setString(3, pe.getStartTime());
			ps.setString(4, pe.getEndTime());
			ps.setString(5, pe.getCreatTime());
			row=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return row;
	}
	
	public int deleteByid(int id) {
		int row=-1;
		conn=DBUtil.getConn();
		try {
			ps=conn.prepareStatement("delete from publishExamPlan where publishExamPlanid=?");
			ps.setInt(1, id);
			row=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return row;
	}
	
	public ArrayList<String> selectAllName() {
		ArrayList<String> list=new ArrayList<String>();
		int row=-1;
		conn=DBUtil.getConn();
		try {
			ps=conn.prepareStatement("select DISTINCT p.paperName from classInfo c,publishExamPlan pu,paperInfo p where pu.isdelete=1 and p.paperid=pu.paperid and pu.classid=c.classid");
			rs=ps.executeQuery();
			while(rs.next()) {
				list.add(rs.getString(1));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return list;
	}
	
	
	public int selectsjts(int paperid) {
		int row=0;
		conn=DBUtil.getConn();
		String sql="select count(*) from (select distinct q.fenzhi,pu.starttime,pu.endtime from questionInfo q,paperTitleInfo p,publishExamPlan pu,paperInfo pi where p.timuid=q.timuid and p.paperid=? and pu.paperid=p.paperid=pi.paperId) p";
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1, paperid);
			rs=ps.executeQuery();
			if(rs.next()) {
				row=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return row;
	}
			
	public ArrayList<PublishExamPlan> selectAllByBjid(int bjid) {
		ArrayList<PublishExamPlan> list =new ArrayList<PublishExamPlan>();
		conn=DBUtil.getConn();
		try {
			ps=conn.prepareStatement("select pu.*,p.paperName from publishExamPlan pu,paperInfo p,classInfo c where c.classId=pu.classid and pu.classid=? and pu.paperid=p.paperid and p.state=2 order by creattime desc");
			ps.setInt(1, bjid);
			rs=ps.executeQuery();
			while(rs.next()) {
				PublishExamPlan p=new PublishExamPlan(rs.getInt(1),rs.getInt(2),rs.getInt(3),rs.getString(4),rs.getString(5),rs.getString(6));
				p.setPaperName(rs.getString(8));
				list.add(p);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return list;
	}
	
	public int selectAllcount(String name) {
		int row=0;
		conn=DBUtil.getConn();
		String sql="select count(*) from classInfo c,publishExamPlan pu,paperInfo p where pu.isdelete=1 and p.paperid=pu.paperid and pu.classid=c.classid";
		if(name!=null&&!"".equals(name)) {
			sql="select count(*) from classInfo c,publishExamPlan pu,paperInfo p where pu.isdelete=1 and p.paperid=pu.paperid and pu.classid=c.classid and p.paperName like'%"+name+"%'";
		}
		try {
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			if(rs.next()) {
				row=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return row;
	}
	
	public ModelPublishExamPlan selectAll(int page,int rows,String name) {
		ModelPublishExamPlan m=new ModelPublishExamPlan();
		ArrayList<PublishExamPlan> list=new ArrayList<PublishExamPlan>();
		conn=DBUtil.getConn();
		page=(page-1)*rows;
		String sql="select pu.*,c.className,p.paperName from classInfo c,publishExamPlan pu,paperInfo p where pu.isdelete=1 and p.paperid=pu.paperid and pu.classid=c.classid limit ?,?";
		if(name!=null&&!"".equals(name)) {
			sql="select pu.*,c.className,p.paperName from classInfo c,publishExamPlan pu,paperInfo p where pu.isdelete=1 and p.paperid=pu.paperid and pu.classid=c.classid and p.paperName like'%"+name+"%' limit ?,?";
		}
		try {
			ps=conn.prepareStatement(sql);
			
			ps.setInt(1, page);
			ps.setInt(2, rows);
			rs=ps.executeQuery();
			while(rs.next()) {
				PublishExamPlan pe=new PublishExamPlan(rs.getInt(1),rs.getInt(2),rs.getInt(3),rs.getString(4),rs.getString(5),rs.getString(6));
				pe.setClassName(rs.getString(8));
				pe.setPaperName(rs.getString(9));
				list.add(pe);
			}
			m.setRows(list);
			m.setTotal(selectAllcount(name));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return m;
	}
}
